#!/bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto
MYSQL_HOME=/usr/bin/mysql
HIVE_HOME=/usr/bin/hive

echo "" >> ./sqoop_liujie.log
start_time=`date +"%Y-%m-%d %H:%M:%S"`
echo "dwb insert  start ${start_time}" >> ./sqoop_liujie.log

${HIVE_HOME} -S -e "
truncate table edu_dwb.dwb_stu_attendance_detail;
"

wait
start_time=`date +"%Y-%m-%d %H:%M:%S"`
echo "dwb truncate   end  ${start_time}" >> ./sqoop_liujie.log

#  由于分区较多，加上presto的内存限制，一次性跑，没有那么大的内存，会把presto跑挂，所以分开跑
sql_head="
insert into hive.edu_dwb.dwb_stu_attendance_detail
select coalesce (t1.school_id,t4.school_id) as school_id,
       coalesce (t1.subject_id,t4.subject_id) as subject_id,
       coalesce (t1.class_id,t4.class_id) as class_id,
       coalesce (t1.studying_student_count,t4.studying_student_count) as studying_student_count,
       coalesce (t2.morning_begin_time,t4.morning_begin_time) as morning_begin_time,
       coalesce (t2.morning_end_time,t4.morning_end_time) as morning_end_time,
       coalesce (t2.afternoon_begin_time,t4.afternoon_begin_time) as afternoon_begin_time,
       coalesce (t2.afternoon_end_time,t4.afternoon_end_time) as afternoon_end_time,
       coalesce (t2.evening_begin_time,t4.evening_begin_time) as evening_begin_time,
       coalesce (t2.evening_end_time,t4.evening_end_time) as evening_end_time,
       coalesce (t3.student_id,t4.student_id) as student_id,
       t4.begin_time as  leave_begin_time,
       t4.end_time as leave_end_time,
       (case when t4.begin_time is not null and t4.begin_time <= concat(t4.studying_date,' ',  t4.morning_end_time) then 1 else 0 end) as  morning_is_leave,
       (case when t4.begin_time is not null
              and t4.begin_time <= concat(t4.studying_date,' ',  t4.afternoon_end_time)
              and t4.end_time > concat(t4.studying_date,' ',  t4.afternoon_begin_time)
             then 1 else 0 end) as afternoon_is_leave,
       (case when t4.begin_time is not null and t4.end_time > concat(t4.studying_date,' ',  t4.evening_begin_time) then 1 else 0 end) as evening_is_leave,
       morning_signin_time,
       afternoon_signin_time,
       evening_signin_time,
       case when morning_signin_time is not null
            and date_diff('minute', date_parse(concat( t1.studying_date,' ', t2.morning_begin_time),'%Y-%m-%d %H:%i:%s'), date_parse( morning_signin_time,'%Y-%m-%d %H:%i:%s'))
                  between -40 and 10 then 0
           when morning_signin_time is not null
            and date_diff('minute', date_parse( concat( t1.studying_date,' ', t2.morning_begin_time),'%Y-%m-%d %H:%i:%s'), date_parse(  morning_signin_time,'%Y-%m-%d %H:%i:%s') ) > 10
           then 1 end as morning_is_late,
       case when afternoon_signin_time is not null
           and date_diff('minute', date_parse(  concat( t1.studying_date,' ', t2.afternoon_begin_time),'%Y-%m-%d %H:%i:%s'), date_parse(  afternoon_signin_time,'%Y-%m-%d %H:%i:%s'))
                  between -40 and 10 then 0
           when afternoon_signin_time is not null
            and date_diff('minute', date_parse(  concat( t1.studying_date,' ', t2.afternoon_begin_time),'%Y-%m-%d %H:%i:%s'), date_parse(  afternoon_signin_time,'%Y-%m-%d %H:%i:%s') )> 10
           then 1 end as afternoon_is_late,
       case when evening_signin_time is not null
           and date_diff('minute', date_parse(  concat( t1.studying_date,' ', t2.evening_begin_time),'%Y-%m-%d %H:%i:%s'), date_parse(  evening_signin_time,'%Y-%m-%d %H:%i:%s'))
                  between -40 and 10 then 0
           when evening_signin_time is not null
            and date_diff('minute', date_parse(  concat( t1.studying_date,' ', t2.evening_begin_time),'%Y-%m-%d %H:%i:%s'), date_parse(  evening_signin_time,'%Y-%m-%d %H:%i:%s')) > 10
           then 1 end as evening_is_late,
       coalesce( t1.studying_date,t4.studying_date) as studying_date
from hive.edu_dwd.fact_class_studying_stu_cnt t1
left join hive.edu_dwd.fact_class_time_table t2
       on t1.class_id = t2.class_id
      and t1.studying_date between t2.use_begin_date and t2.use_end_date
left join (select r.class_id,r.student_id,r.signin_date,
                  max(case when r.flag = 'morning' then r.signin_time end) as morning_signin_time,
                  max(case when r.flag = 'afternoon' then r.signin_time end) as afternoon_signin_time,
                  max(case when r.flag = 'evening' then r.signin_time end) as evening_signin_time
             from hive.edu_dwd.fact_student_signin_record r -- 此处将打卡记录，行转列
            group by r.class_id,r.student_id,r.signin_date
          ) t3 on t3.class_id = t1.class_id and t1.studying_date = t3.signin_date
full join (select a.*,c.studying_date,c.school_id,c.subject_id,c.studying_student_count,
                  e.morning_begin_time,e.morning_end_time,e.afternoon_begin_time,
                  e.afternoon_end_time,e.evening_begin_time,e.evening_end_time
             from hive.edu_dwd.fact_student_leave_apply  a,
                  hive.edu_dwd.fact_class_studying_stu_cnt c,
                  hive.edu_dwd.fact_class_time_table e --此表对于请假数据，缺失对应的作息时间数据
            where a.audit_state = 1
              and a.leave_type = 1
              and a.cancel_state = 0
              and a.valid_state = 1
              and c.studying_date between substr(a.begin_time,1,10) and substr(a.end_time,1,10)
              and c.class_id = a.class_id
              and c.class_id = e.class_id
              and c.studying_date between e.use_begin_date and e.use_end_date
              and not exists (select 1 -- 如果存在部分销假，那逻辑过于复杂，此处省略；此处按只要有销假记录，就是销假全部
                                from hive.edu_dwd.fact_student_leave_apply b -- 销假
                               where a.id = b.old_leave_id
                                 and b.leave_type = 2  -- 销假
                                 and b.audit_state = 1
                                 and b.cancel_state = 0
                                 and b.valid_state = 1
                             ))t4
       on t4.class_id = t1.class_id
      and t3.student_id = t4.student_id
      and t4.studying_date = t1.studying_date
where not exists (select 1 from hive.edu_dwd.fact_course_table_upload_detail d
                    where d.class_id = coalesce (t1.class_id,t4.class_id)
                      and d.class_date = coalesce(t1.studying_date,t4.studying_date)
                      and (d.content is null or d.content = '开班典礼')
                 )

"
condition_sql1=" and substr(coalesce(t1.studying_date,t4.studying_date),1,4) = '2018'"
condition_sql2=" and substr(coalesce(t1.studying_date,t4.studying_date),1,7) between '2019-01' and '2019-06'"
condition_sql3=" and substr(coalesce(t1.studying_date,t4.studying_date),1,7) between '2019-07' and '2019-12'"
condition_sql4=" and substr(coalesce(t1.studying_date,t4.studying_date),1,7) between '2020-01' and '2020-07'"

# 由于分区较多，加上presto的内存限制，一次性跑，没有那么大的内存，会把presto跑挂，所以分开跑
sql_batch1=${sql_head}${condition_sql1}
sql_batch2=${sql_head}${condition_sql2}
sql_batch3=${sql_head}${condition_sql3}
sql_batch4=${sql_head}${condition_sql4}

echo "${sql_batch1}" >> ./sqoop_liujie.log

${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
${sql_batch1}
"
wait
start_time=`date +"%Y-%m-%d %H:%M:%S"`
echo "dwb insert sql_batch1   end  ${start_time}" >> ./sqoop_liujie.log

${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
${sql_batch2}
"
wait
start_time=`date +"%Y-%m-%d %H:%M:%S"`
echo "dwb insert sql_batch2   end  ${start_time}" >> ./sqoop_liujie.log


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
${sql_batch3}
"
wait
start_time=`date +"%Y-%m-%d %H:%M:%S"`
echo "dwb insert sql_batch3   end  ${start_time}" >> ./sqoop_liujie.log


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
${sql_batch4}
"
start_time=`date +"%Y-%m-%d %H:%M:%S"`
echo "dwb insert sql_batch4   end  ${start_time}" >> ./sqoop_liujie.log


start_time=`date +"%Y-%m-%d %H:%M:%S"`
echo "dwb insert  end ${start_time}" >> ./sqoop_liujie.log

